﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TT.Module.sysLogin;
using System.Data;
using SfcorpDLL.ety;

namespace SfcorpDLL.dao
{
    public class daoMainTag
    {
        etyBaeSystemParamter basSysParam;
        public daoMainTag(etyBaeSystemParamter ieyBasClassDeclare)
        {
            basSysParam = ieyBasClassDeclare;
        }
        public DataTable GetLastFrtAgyBns(string sta)
        {
//            string sql = @"select CONTRACT_NO,disp_datetime(contract_date) CONTRACT_DATE,REC_NAM,CUSTOMER_ID,CUSTOMER_NAM,FEE_WAY_ID,FEE_WAY_NAM,CLIENT_ID,CLIENT_CONTACT,PORT_ID,PORT_NAM,I_E_ID,I_E_NAM,COLLABORATE_ID,COLLABORATE_NAM,CNTR_CORP_COD,CNTR_CORP_NAM,COUNT(*) CNTR_COUNT 
//                from VW_FRT_AGY_BNS where CONTRACT_DATE>= to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-MM-dd') ";
//            if (sta == "D")
//                sql += @" and bns_status='D' ";
//            else
//                sql += @" and bns_status<>'D' ";
//            sql += @" group by CONTRACT_NO,CONTRACT_DATE,REC_NAM,CUSTOMER_ID,CUSTOMER_NAM,FEE_WAY_ID,FEE_WAY_NAM,CLIENT_ID,CLIENT_CONTACT,PORT_ID,PORT_NAM,I_E_ID,I_E_NAM,COLLABORATE_ID,COLLABORATE_NAM,CNTR_CORP_COD,CNTR_CORP_NAM
//            order by CONTRACT_DATE desc ";
            string sql = @"select CONTRACT_NO,disp_datetime(contract_date) CONTRACT_DATE,REC_NAM,CUSTOMER_ID,CUSTOMER_NAM,FEE_WAY_ID,FEE_WAY_NAM,CLIENT_ID,CLIENT_CONTACT
,PORT_ID,PORT_NAM,I_E_ID,I_E_NAM,COLLABORATE_ID,COLLABORATE_NAM,CNTR_CORP_COD,CNTR_CORP_NAM,COUNT(*) CNTR_COUNT ,BNS_STATUS_D,sum(c.amount) O_AMOUNT
from VW_FRT_AGY_BNS b
left join charge_dtl c on b.FABC_GKEY=c.bill_key and c.charge_mod='CASH' 
where CONTRACT_DATE>= to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-MM-dd')  
group by CONTRACT_NO,CONTRACT_DATE,REC_NAM,CUSTOMER_ID,CUSTOMER_NAM,FEE_WAY_ID,FEE_WAY_NAM,CLIENT_ID,CLIENT_CONTACT,PORT_ID
,PORT_NAM,I_E_ID,I_E_NAM,COLLABORATE_ID,COLLABORATE_NAM,CNTR_CORP_COD,CNTR_CORP_NAM,BNS_STATUS_D
order by CONTRACT_DATE desc ";
            return basSysParam.SqlHelp.ExecuteReader(sql);
        }
    }
}
